package com.scalar.chiptrack.operations.processtypesetup.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.UOM;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.processtypesetup.ProcessParameters;
import com.scalar.chiptrack.operations.processtypesetup.ProcessSetup;
import com.scalar.chiptrack.operations.processtypesetup.forms.ProcessTypeSetupForm;
import org.apache.struts.util.LabelValueBean;

public class ProcessTypeSetupDAO
{
	public static boolean isProcessExists( String ProcessName ) throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;

		if( ProcessName == null )
		{
			return false;
		}
		try
		{
			conn 	= DataSource.getConnection();
			pstmt 	= conn.prepareStatement ( "select base_process_type from process_types_table where process_type = ? " );
			pstmt.setString ( 1, ProcessName );
			rs = pstmt.executeQuery();
			
			if ( rs.next() == false )
			{
				return false;
			}
			else
			{
				return true;
			}
		}
		catch (Exception sqlex)
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not check ProcessName in database", sqlex );
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
				if (pstmt != null)
				{
					pstmt.close();
				}
				if (conn != null)
				{
					conn.close();
				}
			}
			catch ( SQLException excp )
			{
				System.out.println("Sql exception occured in isProcessExists method");
			}
		}
	}

    public static boolean isProcessOrderExists( String processOrderNo ) throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;

		if( processOrderNo == null )
		{
			return false;
		}
		try
		{
			conn 	= DataSource.getConnection();
			pstmt 	= conn.prepareStatement ( "select base_process_type from process_types_table where sorting_order = ? " );
			pstmt.setString ( 1, processOrderNo );
			rs = pstmt.executeQuery();

			if ( rs.next() == false )
			{
				return false;
			}
			else
			{
				return true;
			}
		}
		catch (Exception sqlex)
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not check ProcessName in database", sqlex );
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
				if (pstmt != null)
				{
					pstmt.close();
				}
				if (conn != null)
				{
					conn.close();
				}
			}
			catch ( SQLException excp )
			{
				System.out.println("Sql exception occured in isProcessExists method");
			}
		}
	}
    // Submitting process details into the database at the time of creating a new process.
	public static boolean saveProcessDetails( ProcessTypeSetupForm form ) throws ScalarException
	{
		Connection conn 			= null;
		PreparedStatement pstmt 	= null;
		ResultSet rs				= null;
		boolean success 			= false;
		ProcessParameters parameters= null;
		int processTypeId			= -1;
		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement ( "insert into process_types_table ( process_type , base_process_type, SORTING_ORDER, Is_Wip) values ( ? , ? , ?, ? )" );
			pstmt.setString ( 1, form.getProcessName() );
			pstmt.setString ( 2, form.getProcessType() );
            pstmt.setString ( 3, form.getProcessOrder() );
            pstmt.setInt    ( 4, "WIP".equalsIgnoreCase(form.getInventoryType() ) ? 0 : 1 );

            pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
			
			pstmt 	= conn.prepareStatement ("SELECT @@IDENTITY as ProcessTypeId");
			rs 		= pstmt.executeQuery();
			if ( rs.next() != false ) 
			{
				processTypeId =  rs.getInt ( "ProcessTypeId" );
			}
			
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			pstmt = conn.prepareStatement ( "insert into default_process_parameter_table ( ProcessTypeId , dpp_parameter, dpp_parameter_value, dpp_parameter_units,DPP_System_Parameter ) values ( ? , ? , ? , ?,? )" );
			
			for( int i = 0; i < form.getProcessParameters().size(); i++ )
			{
				parameters = (ProcessParameters) form.getProcessParameters().get( i );
				if( parameters.getParamName() != null && parameters.getParamName().trim().length() > 0 )
				{
					pstmt.setInt 	( 1, processTypeId );
					pstmt.setString ( 2, parameters.getParamName() );
					pstmt.setString ( 3, parameters.getParamVal() );
					pstmt.setString ( 4, parameters.getParamUnit() );
                    pstmt.setInt    ( 5, parameters.getSysParam() ? 0 : 1);
					pstmt.addBatch();
				}
			}
			pstmt.executeBatch();
			success = true;
		}
		catch ( Exception sqle )
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not insert Process Setup into database", sqle);
		}
		finally
		{
			if( conn != null )
			{
				try
				{
					if (pstmt != null)
					{
						pstmt.close();
					}
					if( success == true )
					{
                        conn.commit();
                    }
                    else
                    {
                    	conn.rollback();
                    }
                    conn.close();
				}
                catch (SQLException e)
                {
	                e.printStackTrace();
                }
			}
		}
		return success;
	}

	public static ArrayList getProcesses( String processName, String processType ) throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
		ArrayList processList 	= new ArrayList();
		ProcessSetup process	= null;

		try
		{
			conn 	= DataSource.getConnection();
			pstmt 	= conn.prepareStatement ( "SELECT ProcessTypeId, process_type, base_process_type, Sorting_Order, Is_Wip  from Process_Types_Table where process_type like ? and base_process_type like ? order by Sorting_Order" );
			
			if( processName == null )
			{
				processName = "";
			}else{
                processName = processName.trim() ;
            }
			
			if( processType == null )
			{
				processType = "";
			}
			
			pstmt.setString ( 1, processName + "%" );
			pstmt.setString ( 2, processType + "%" );
						
			rs = pstmt.executeQuery();
			
			while ( rs.next() )
			{
				process = new ProcessSetup();
				process.setProcessTypeId( rs.getInt( "ProcessTypeId" ) );
				process.setProcess 		( rs.getString ( "process_type" ) );
				process.setBaseProcess	( rs.getString( "base_process_type" ) );
                process.setProcessOrderNo( rs.getString("Sorting_order") );
                process.setInventoryType( rs.getInt("Is_Wip") == 0 ? "WIP" : "Inventory");
                processList.add ( process );
			}
		}
		catch ( Exception sqlex )
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not get ProcessList from database", sqlex );
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
				if (pstmt != null)
				{
					pstmt.close();
				}
				if (conn != null)
				{
					conn.close();
				}
			}
			catch(SQLException e)
			{
				throw new ScalarException( "SqlException Occurred .." );
			}
		}
		return processList;
	}


    public static ProcessTypeSetupForm loadProcess( ProcessTypeSetupForm form ) throws ScalarException
    {
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs			= null;
		ProcessParameters parameters = null;
		ArrayList processParametersList = new ArrayList();
    	
    	try
    	{
    		conn  = DataSource.getConnection();
    		pstmt = conn.prepareStatement( "select Process_Type, Base_Process_Type,Sorting_Order,Is_Wip from process_types_table where ProcessTypeId = ?" );
    		pstmt.setInt( 1, form.getProcessTypeId() );
    		
    		rs = pstmt.executeQuery();
    		if( rs.next() )
    		{
    			form.setProcessName( rs.getString( "Process_Type" ) );
    			form.setProcessType( rs.getString( "Base_Process_Type" ) );
                form.setProcessOrder( rs.getString("Sorting_Order"));
                form.setInventoryType( rs.getInt("Is_Wip") == 0 ? "WIP" : "inventory" );
            }
    		
    		rs.close();
    		rs = null;
    		pstmt.close();
    		pstmt = null;
    		
    		//pstmt = conn.prepareStatement( "select * from Default_Process_Parameter_Table where ProcessTypeId = ? order By DPP_System_Parameter desc" );
            pstmt = conn.prepareStatement( "select * from Default_Process_Parameter_Table where ProcessTypeId = ? order By DPP_System_Parameter desc, ProcessParameterId asc" );
			pstmt.setInt( 1, form.getProcessTypeId() );
			rs = pstmt.executeQuery();
			
			while( rs.next() )
			{
				parameters = new ProcessParameters();
				parameters.setProcessParameterId( rs.getInt( "ProcessParameterId" ) );
				parameters.setParamName	( rs.getString("DPP_Parameter") );
				parameters.setParamVal	( rs.getString("DPP_Parameter_Value") );
				parameters.setParamUnit	( rs.getString("DPP_Parameter_Units") );
                parameters.setSysParam( rs.getInt("DPP_System_Parameter")== 0 ? true : false);
				processParametersList.add( parameters );
			}
			form.setProcessParameters( processParametersList );
    	}
    	catch ( SQLException se )
    	{
    		se.printStackTrace();
    		throw new ScalarException ( "Could not fetch the process details" );
    	}
    	finally
    	{
			try
			{
				if ( rs != null ) { rs.close(); }
				if ( pstmt != null ) { pstmt.close(); }
				if ( conn != null) { conn.close(); }
			}catch( SQLException e )
			{
				System.out.println( "SQLException while fetching process details" );
			}
    	}
        return form;
    }

    public static void updateProcessDetails( ProcessTypeSetupForm form ) throws ScalarException
    {
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ProcessParameters parameters= null;
		boolean success = false;
		try
		{
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( "UPDATE PROCESS_TYPES_TABLE SET Sorting_Order = ? , Is_Wip = ? where ProcessTypeId = ?" );
            pstmt.setString( 1, form.getProcessOrder() );
            pstmt.setInt( 2, form.getInventoryType().equalsIgnoreCase("WIP") ? 0 : 1 );
            pstmt.setInt( 3, form.getProcessTypeId() );
            pstmt.executeUpdate();

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            for( int i = 0; i < form.getProcessParameters().size(); i++ )
			{            
				parameters = (ProcessParameters) form.getProcessParameters().get( i );
				
				if( parameters.getDeleteParam() != null && parameters.getDeleteParam().equalsIgnoreCase( "on" ) )
				{
					if( parameters.getProcessParameterId() != -1 )
					{
						pstmt = conn.prepareStatement( "delete from default_process_parameter_table where ProcessParameterId = ?" );
						pstmt.setInt( 1, parameters.getProcessParameterId() );
					}
				}
				else
				{
					if( parameters.getProcessParameterId() == -1 && parameters.getParamName() != null && parameters.getParamName().trim().length() > 0 )
					{
						pstmt = conn.prepareStatement ( "insert into default_process_parameter_table ( ProcessTypeId , dpp_parameter, dpp_parameter_value, dpp_parameter_units, dpp_System_Parameter ) values ( ? , ? , ? , ?, ? )" );
						pstmt.setInt 	( 1, form.getProcessTypeId() );
						pstmt.setString ( 2, parameters.getParamName() );
						pstmt.setString ( 3, parameters.getParamVal() );
						pstmt.setString ( 4, parameters.getParamUnit() );
                        pstmt.setInt    ( 5, parameters.getSysParam() ? 0 : 1);
					}
					else
					{
                        if ( parameters.getIsEditable() ) //added by sujan on 30th dec 2004. to update the process parameters which r editable
                        {
                            pstmt = conn.prepareStatement ( "UPDATE Default_Process_Parameter_Table SET DPP_Parameter = ?, DPP_Parameter_value = ?, DPP_Parameter_Units = ?, DPP_System_Parameter = ? WHERE ProcessParameterId = ? " );
                            pstmt.setString ( 1, parameters.getParamName() );
                            pstmt.setString ( 2, parameters.getParamVal() );
                            pstmt.setString ( 3, parameters.getParamUnit() );
                            pstmt.setInt ( 4, parameters.getSysParam() ? 0 : 1 );
                            pstmt.setInt ( 5, parameters.getProcessParameterId() );
                        }
                        else
                        {
						    pstmt = conn.prepareStatement ( "update default_process_parameter_table set dpp_parameter_value = ? where ProcessParameterId = ? " );
						    pstmt.setString	( 1, parameters.getParamVal() );
						    pstmt.setInt 	( 2, parameters.getProcessParameterId() );
                        }
					}
				}
				pstmt.executeUpdate();
				pstmt.clearParameters();
				pstmt.close();
                pstmt = null;
            }
			success = true;	
		}
		catch( SQLException se )
		{
			se.printStackTrace();
			throw new ScalarException ( "Could not update the process parameters" );
		}
		catch ( ScalarException e )
		{
			e.printStackTrace();
			throw new ScalarException ( "Could not update the process parameters" );
		}
		finally
		{
			try
			{
				if ( conn != null )
				{
					if( success == true )
					{
						conn.commit();
					}
					else
					{
						conn.rollback();
					}
				}
				if ( pstmt != null ) { pstmt.close(); }
				if ( conn != null  ) { conn.close(); }
			}catch( SQLException e )
			{
				System.out.println( "SQLException while fetching process details" );
			}
		}
    }
    
	public static ArrayList getUnitsOfMesurment() throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
		ArrayList uomList 		= new ArrayList();
		UOM uom = null;
		
		try
		{
			conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT UOM, BASEUOM, MAXVALUE, MINVALUE, ISREQUIRED FROM Units_Of_Mesurment" );
            rs = pstmt.executeQuery();
            
            while( rs.next() )
            {
            	uom = new UOM();
            	uom.setUom( rs.getString( "UOM" ) );
				uom.setBaseUom( rs.getString( "BASEUOM" ) );
				uom.setMaxValue( rs.getString( "MAXVALUE" ) );
				uom.setMinValue( rs.getString( "MINVALUE" ) );
				uom.setRequired( rs.getBoolean( "ISREQUIRED" ) );
				uomList.add( uom );
            }
		}
		catch( SQLException se )
		{
			se.printStackTrace();
			throw new ScalarException ( "Could not load Units of Mesurment" );
		}
		finally
		{
			try
			{
				if ( pstmt != null ) { pstmt.close(); }
				if ( conn != null  ) { conn.close(); }
			}catch( SQLException e ){}
		}
		return uomList;
	}

	/**
	 * @param processTypeSetupForm
	 */
	public static void deleteProcessDetails(ProcessTypeSetupForm processTypeSetupForm)throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		boolean success 		= false; 

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( "DELETE FROM default_process_parameter_table WHERE processTypeId = ?" );
			pstmt.setInt( 1, processTypeSetupForm.getProcessTypeId() );
			pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
            
			pstmt = conn.prepareStatement( "DELETE FROM process_types_table WHERE processTypeId = ?" );
			pstmt.setInt( 1, processTypeSetupForm.getProcessTypeId() );
			pstmt.executeUpdate();
			success = true;
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Delete the Process" );
		}
		finally
		{
			try
			{
				if( success )
				{
					conn.commit();	
				}
				else
				{
					conn.rollback();
				}
				
				if ( pstmt != null ) { pstmt.close(); }
				if ( conn != null  ) { conn.close(); }
			}catch( SQLException e ){}
		}
	}

	/**
	 * @param processTypeSetupForm
	 */
	public static boolean checkProcessUsed( ProcessTypeSetupForm processTypeSetupForm ) throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
		int count				= 0;

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( "SELECT Count(*) RECORDS FROM PROCESS_STEP WHERE PS_TYPE = ?" );
			pstmt.setString( 1, processTypeSetupForm.getProcessName() );
			rs = pstmt.executeQuery();
            
			if( rs.next() )
			{
            	count = rs.getInt( "RECORDS" );
			}
			
			if( count > 0 )
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Search the Process Step table for the Process" );
		}
		finally
		{
			try
			{
				if ( rs != null ){ rs.close(); }
				if ( pstmt != null ) { pstmt.close(); }
				if ( conn != null  ) { conn.close(); }
			}catch( SQLException e ){}
		}
	}

	/**
	 * @param processType
	 */
	public static ArrayList getSystemParameters( String processType ) throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
		ProcessParameters parameter = null;
		ArrayList parameterList = new ArrayList();

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( "SELECT PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE FROM SYSTEM_PARAMETER_TABLE WHERE PROCESS_TYPE = ?" );
			pstmt.setString( 1, processType );
			rs = pstmt.executeQuery();
            
			while( rs.next() )
			{
				parameter = new ProcessParameters();
				parameter.setParamName( rs.getString( "PROCESS_PARAMETER" ) );
				parameter.setParamUnit( rs.getString( "PARAMETER_UNITS" ) );
				parameter.setParamVal( rs.getString( "PARAMETER_VALUE" ) );
				parameter.setProcessParameterId( -1 );
				parameter.setSysParam( true );
				parameterList.add( parameter );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not fetch the system parameters for the Process" );
		}
		finally
		{
			try
			{
				if ( rs != null ){ rs.close(); }
				if ( pstmt != null ) { pstmt.close(); }
				if ( conn != null  ) { conn.close(); }
			}
			catch( SQLException e ){}
		}
		return parameterList;
	}

    public static void setIsEditable ( ProcessTypeSetupForm processTypeSetupForm ) throws ScalarException
    {
        Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
        if ( processTypeSetupForm == null )
        {
            return;
        }
        ArrayList processParametersList = processTypeSetupForm.getProcessParameters();
        ProcessParameters processParameter = null;
        int processParametersSize = ( processParametersList == null ) ? 0 : processParametersList.size();
        if ( processParametersSize > 0 )
        {
            try
            {
                conn = DataSource.getConnection();
                pstmt = conn.prepareStatement( "select COUNT(*) from Process_Parameter, Process_Step where\n " +
                        "Process_Parameter.ProcessStep_id = Process_Step.ProcessStep_id \n " +
                        "and Parameter_Key = ? and PS_Type = ? and PS_Type_Name = ?" );
                pstmt.setString( 2, processTypeSetupForm.getProcessName() );
                pstmt.setString( 3, processTypeSetupForm.getProcessType() );
                int count = 0;
                for ( int i = 0; i < processParametersSize; i++ )
                {
                    processParameter = ( ProcessParameters )processParametersList.get ( i );
                    if ( processParameter.getSysParam() )
                    {
                         processParameter.setIsEditable( false );
                    }
                    else
                    {
                        pstmt.setString ( 1, processParameter.getParamName() );
                        rs = pstmt.executeQuery ( );
                        if ( rs.next() )
                        {
                            count = rs.getInt ( 1 );
                            if ( count == 0 )
                            {
                                processParameter.setIsEditable( true );
                            }
                            else
                            {
                                processParameter.setIsEditable( false );
                            }
                        }
                        rs.close();
                    }
                }
            }
            catch ( Exception e )
            {
                e.printStackTrace();
                throw new ScalarException ( "Exception While setting the IsEditable Property For Process Parameters");
            }
            finally
            {
                if ( rs != null )
                {
                    try { rs.close(); } catch ( SQLException e ){ }
                }
                if ( pstmt != null )
                {
                    try { pstmt.close(); } catch ( SQLException e ) { }
                }
                if ( conn != null  )
                {
                    try { conn.close(); } catch( SQLException e ){}
                }
            }
        }
    }

    public static HashMap getComboMapForProcessType ( DBTransaction dbTransaction, String processType ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;
        HashMap comboMap = new HashMap();
        if ( processType == null || processType.trim().length() == 0 )
        {
            return null;
        }
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT Process_Parameter, Process_Parameter_Value FROM Process_Parameter_Values WHERE Process_Type = ?" );
            pstmt.setString ( 1, processType.trim() );
            rset = pstmt.executeQuery();
            if ( rset != null )
            {
                String parameterName        = null;
                String parameterValue       = null;
                ArrayList parameterValues   = null;
                while ( rset.next() )
                {
                    parameterName   = rset.getString( "Process_Parameter" );
                    parameterValue  = rset.getString ( "Process_Parameter_Value" );
                    parameterValues = ( ArrayList ) comboMap.get ( parameterName );
                    if( parameterValues == null )
                    {
                        parameterValues = new ArrayList ();
                        comboMap.put ( parameterName, parameterValues );
                    }
                    parameterValues.add ( new LabelValueBean( parameterValue, parameterValue ) );
                }
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--------EXCEPTION IN PREPARING COMBO MAP--------" );
            e.printStackTrace();
            throw new ScalarException ( "could not prepare combo map", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( Exception e )
                        {
                            throw new ScalarException ( "Could not commit", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( Exception e )
                        {
                            throw new ScalarException ( "Could Not rollback", e );
                        }
                    }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return comboMap;
    }
    public static ArrayList getProcessParameterValues ( DBTransaction dbTransaction, String processType, String processParameter ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement   pstmt   = null;
        ResultSet rset              = null;
        boolean success             = false;
        ArrayList parameterValues   = new ArrayList();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT Process_Parameter_Value FROM Process_Parameter_Values WHERE Process_Type = ? AND Process_Parameter = ?" );
            pstmt.setString ( 1, processType);
            pstmt.setString( 2, processParameter);
            rset = pstmt.executeQuery();
            String parameterValue = null;
            while ( rset.next() )
            {
                parameterValue = rset.getString ( "Process_Parameter_Value" );
                //parameterValues.add ( new LabelValueBean( parameterValue, parameterValue ) );
                parameterValues.add ( parameterValue );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--------EXCEPTION IN GETTING PROCESS PARAMETER VALUES--------" );
            e.printStackTrace();
            throw new ScalarException ( "could not prepare combo map", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( Exception e )
                        {
                            throw new ScalarException ( "Could not commit", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( Exception e )
                        {
                            throw new ScalarException ( "Could Not rollback", e );
                        }
                    }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return parameterValues;
    }

    public static void saveProcessParameterValues ( DBTransaction dbTransaction, String processType, String processParameter, ArrayList parameterValues ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        if ( parameterValues == null )
        {
            return;
        }
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "DELETE FROM Process_Parameter_Values WHERE Process_Type = ? AND Process_Parameter = ?" );
            pstmt.setString ( 1, processType );
            pstmt.setString ( 2, processParameter );
            pstmt.executeUpdate();

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
            pstmt = conn.prepareStatement ( "INSERT INTO Process_Parameter_Values(Process_Type, Process_Parameter, Process_Parameter_Value) VALUES ( ?, ?, ? )" );
            pstmt.setString ( 1, processType );
            pstmt.setString ( 2, processParameter );

            int parameterValuesSize = parameterValues.size();
            String parameterValue   = null;

            for ( int i = 0; i < parameterValuesSize; i++ )
            {
                parameterValue  = ( String )parameterValues.get ( i );
                pstmt.setString ( 3, parameterValue );

                pstmt.addBatch();
            }
            pstmt.executeBatch();

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
            //TO INSERT PROCESS PARAMETER DEFAULT LEGACY VALUES
            pstmt = conn.prepareStatement ( "INSERT INTO Process_Parameter_Values( Process_Type, Process_Parameter, Process_Parameter_Value) " +
                    "SELECT DISTINCT '" + processType + "', '" + processParameter + "', DPP_Parameter_value FROM Default_Process_Parameter_Table DPP\n" +
                    " WHERE " +
                    "\tDPP.processTypeId IN \n" +
                    "\t( SELECT ProcessTypeId FROM Process_Types_Table WHERE Base_Process_Type = ? )\n" +
                    "\tAND DPP.DPP_Parameter = ?\n" +
                    "\tAND DPP_Parameter_Value NOT IN \n" +
                    "\t( SELECT Process_Parameter_Value FROM Process_Parameter_Values \n" +
                    "\t \tWHERE Process_Type = ? AND Process_Parameter = DPP.DPP_Parameter )" );
            pstmt.setString ( 1, processType );
            pstmt.setString ( 2, processParameter );
            pstmt.setString ( 3, processType );
            pstmt.executeUpdate();

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
            //TO INSERT PROCESS PARAMETER VALUES WHICH ARE SAVED IN PRODUCT SETUP SCREEN
            pstmt = conn.prepareStatement ( "INSERT INTO Process_Parameter_Values( Process_Type, Process_Parameter, Process_Parameter_Value) \n" +
                    "SELECT DISTINCT '" + processType +"', '" + processParameter +"', Parameter_Value FROM Process_Parameter \n" +
                    " WHERE " +
                    "\tProcessStep_ID IN ( SELECT ProcessStep_ID FROM Process_Step WHERE PS_Type_Name = ? )\n" +
                    "\tAND Parameter_Key = ?\n" +
                    "\tAND Parameter_Value NOT IN ( SELECT  Process_Parameter_Value FROM Process_Parameter_Values \n" +
                    "\t \tWHERE Process_Type = ? AND Process_Parameter = Parameter_Key ) " );
            pstmt.setString ( 1, processType );
            pstmt.setString ( 2, processParameter );
            pstmt.setString ( 3, processType );
            pstmt.executeUpdate();

            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--------EXCEPTION IN SAVE PROCESS PARAMETER VALUES--------" );
            e.printStackTrace();
            throw new ScalarException ( "could not save parameter values", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( Exception e )
                        {
                            throw new ScalarException ( "Could not commit", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( Exception e )
                        {
                            throw new ScalarException ( "Could Not rollback", e );
                        }
                    }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }

    }

    public static HashMap getComboVectorMapForProcessType ( DBTransaction dbTransaction, String processType ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;
        HashMap comboMap = new HashMap();
        if ( processType == null || processType.trim().length() == 0 )
        {
            return null;
        }
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT Process_Parameter, Process_Parameter_Value FROM Process_Parameter_Values WHERE Process_Type = ?" );
            pstmt.setString ( 1, processType.trim() );
            rset = pstmt.executeQuery();
            if ( rset != null )
            {
                String parameterName        = null;
                String parameterValue       = null;
                Vector parameterValues   = null;
                while ( rset.next() )
                {
                    parameterName   = rset.getString( "Process_Parameter" );
                    parameterValue  = rset.getString ( "Process_Parameter_Value" );
                    parameterValues = ( Vector ) comboMap.get ( parameterName );
                    if( parameterValues == null )
                    {
                        parameterValues = new Vector ();
                        comboMap.put ( parameterName, parameterValues );
                    }
                    parameterValues.add ( parameterValue  );
                }
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--------EXCEPTION IN PREPARING COMBO MAP--------" );
            e.printStackTrace();
            throw new ScalarException ( "could not prepare combo map", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( Exception e )
                        {
                            throw new ScalarException ( "Could not commit", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( Exception e )
                        {
                            throw new ScalarException ( "Could Not rollback", e );
                        }
                    }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return comboMap;
    }

}